﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmClientsAccStatements

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Sub FillInsuranceCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select Distinct InsurCompany From ClientsAcc Where ClientID=" & Me.txtClientNo.Text.Trim & _
                                      " and InsurCompany Is Not Null and InsurCompany<>N''", cnn)
            Dim Reader As SqlDataReader

            Me.CombInsurComp.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombInsurComp.Items.Add(Reader.Item(0))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub txtClientNo_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtClientNo.KeyUp
        If Me.txtClientNo.Text.Trim.Length = 0 Then
            Exit Sub
        ElseIf e.KeyCode = Keys.Enter Then
            Try
                Me.Cursor = Cursors.Default

                Me.txtClientName.Text = GetClientName(CInt(Me.txtClientNo.Text.Trim))


                If Me.txtClientName.Text.Trim.Length > 0 Then
                    FillInsuranceCompanies()
                End If

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub txtClientNo_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtClientNo.TextChanged
        Me.txtClientName.Clear()
        Me.CombInsurComp.Items.Clear()
        Me.CrystalReportViewer1.ReportSource = Nothing
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim a As New frmSearchCustID
        a.ShowDialog()

        If SelClientID = 0 Then
            Exit Sub
        End If

        Me.txtClientNo.Text = SelClientID
        Me.txtClientName.Text = SelClientName
        FillInsuranceCompanies()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.ErrProv.Clear()
        If Me.txtClientName.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtClientNo, "Please fill in all details")
            Me.txtClientNo.Focus()

        ElseIf Me.RInsurComp.Checked = True And Me.CombInsurComp.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombInsurComp, "Please fill in all details")
            Me.txtClientNo.Focus()

        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim StrCond As String
                If Me.RAll.Checked = True Then
                    StrCond = ""

                Else
                    StrCond = " and InsurCompany=N'" & Me.CombInsurComp.SelectedItem & "'"
                End If

                Dim dap As New SqlDataAdapter("(Select N'" & Me.DTPPeriodFRm.Value.AddDays(-1).ToShortDateString & " 10:10:10' TransDate," & _
                                              Me.txtClientNo.Text & " ClientID,N'' ClientName," & _
                                              "N'Open Balance' InsurCompany,N'' InsurCompanyAccNo,N'' PolicyNo, N'' EndorsNo,N'' PaymentDetails," & _
                                              "0 TotalIn,0 TotalOut,Sum(TotalIn)-Sum(TotalOut) SNo From ClientsAcc " & _
                                              "Where ClientID=" & Me.txtClientNo.Text.Trim & " and TransDate < N'" & _
                                              Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & StrCond & ") Union All " & _
                                              "(Select TransDate,ClientID,ClientName,InsurCompany,InsurCompanyAccNo," & _
                                              "PolicyNo,EndorsNo,PaymentDetails,TotalIn,TotalOut,TotalIn-TotalOut From ClientsAcc " & _
                                              "Where ClientID=" & Me.txtClientNo.Text.Trim & " and TransDate >= N'" & _
                                              Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' and TransDate <= N'" & _
                                              Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & StrCond & ")", cnn)
                Dim das As New DataSet

                dap.SelectCommand.CommandTimeout = 6000
                dap.Fill(das, "ClientsAcc")

                Dim rpt As New ClientsAccStatements
                rpt.SetDataSource(das)

                'Create Parameters

                Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
                Dim crParameterFieldDefinitions As ParameterFieldDefinitions
                Dim crParameterFieldLocation As ParameterFieldDefinition
                Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
                crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

                'MinDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("ClientName")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.txtClientName.Text
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                'MinDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


                'MaxDate PARAMETER
                crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
                crParameterValues = crParameterFieldLocation.CurrentValues
                crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
                crParameterValues.Add(crParameterDiscreteValue)
                crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                Me.CrystalReportViewer1.ReportSource = rpt

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub RAll_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RAll.CheckedChanged
        If Me.RInsurComp.Checked = True Then
            Me.CombInsurComp.Enabled = True
            Me.CombInsurComp.SelectedIndex = -1

        Else
            Me.CombInsurComp.Enabled = False
            Me.CombInsurComp.SelectedIndex = -1
        End If
    End Sub

    Private Sub frmClientsAccStatements_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
    End Sub
End Class